﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Reflection;
using System.Text;

namespace Kators.BusinessObjects
{
    public class DataBaseAccess<T> where T : DataBaseAccess<T>
    {
        //cbc
        private static string connectionString = Configuration.DbConnection;

        private bool _isNew;

        private readonly List<string> _autoUpdateDateProperties = new List<string> 
                                                                     {  
                                                                          "updatedon",
                                                                          "lastupdatedon",
                                                                          "lastupdated"
                                                                     };

        private readonly List<string> _autoDateProperties = new List<string>
                                                                {
                                                                    "createdon",
                                                                    "updatedon",
                                                                    "lastupdatedon",
                                                                    "lastupdated"
                                                                };


        public DataBaseAccess()
        {
            var type = typeof(T);
            var properties = type.GetProperties();
            var primaryKeyProperty = GetPrimaryKeyProperty(properties);

            primaryKeyProperty.SetValue(this, Guid.NewGuid(), null);

            _isNew = true;

        }

        private PropertyInfo GetPrimaryKeyProperty(PropertyInfo[] properties)
        {
            var primaryKeys = new List<PropertyInfo>();
            Array.ForEach(properties, x =>
                                          {
                                              if (x.GetCustomAttributes(typeof(PrimaryKeyAttribute), false).Length > 0)
                                              {
                                                  primaryKeys.Add(x);
                                              }
                                          });

            if (primaryKeys.Count > 1)
                throw new Exception("Class can only have one PrimaryKey attribute.");

            if (primaryKeys.Count < 1)
                throw new Exception("Class must have a property with a PrimaryKey attribute.");

            return primaryKeys[0];
        }

        public string GetSqlValueFromObject(object value)
        {
            if (value == null)
            {
                return "NULL";
            }

            var typeName = value.GetType().Name.ToLower();

            switch (typeName)
            {
                case "single":
                case "double":
                case "decimal":
                case "int32":
                    return "'" + value + "'";
                case "string":
                    return "'" + value.ToString().Replace("'", "''") + "'";
                case "guid":
                    if ((Guid)value == Guid.Empty)
                        return "NULL";
                    return "'" + (Guid)value + "'";
                case "datetime":
                    if ((DateTime)value == DateTime.MinValue)
                        return "NULL";
                    return "'" + (DateTime)value + "'";
                case "boolean":
                    return "'" + Convert.ToInt32((bool)value) + "'";
                default:
                    throw new Exception("Could not determine " + typeName + " type from object.");
            }

        }

        private static string GetTableNameFromType(Type type)
        {
            var table = type.Name;

            var attributes = type.GetCustomAttributes(typeof(TableNameAttribute), false);

            if (attributes.Length > 0)
            {
                var tableNAmeAttribute = attributes[0];

                if (tableNAmeAttribute as TableNameAttribute == null)
                    throw new Exception("Class is missing the TableNameAttribute");

                table = (tableNAmeAttribute as TableNameAttribute).TableName;
            }

            return table;
        }

        private static string GetPrimaryKeyName(PropertyInfo[] properties)
        {
            var primaryKeys = new List<PropertyInfo>();

            Array.ForEach(properties, x =>
            {
                if (x.GetCustomAttributes(typeof(PrimaryKeyAttribute), false).Length > 0)
                {
                    primaryKeys.Add(x);
                }
            });


            if (primaryKeys.Count > 1)
                throw new Exception("Class can only have one PrimaryKey attribute.");

            if (primaryKeys.Count < 1)
                throw new Exception("Class must have a property with a PrimaryKey attribute.");

            primaryKeys.ForEach(x =>
            {
                if (x.PropertyType != typeof(Guid))
                {
                    throw new Exception("Primary key can only be of type Guid.");
                }
            });

            var primaryKeyName = string.Empty;

            var primaryKeyAttribute = primaryKeys[0].GetCustomAttributes(typeof(PrimaryKeyAttribute), false);

            if (primaryKeyAttribute.Length > 0 && (primaryKeyAttribute[0] as PrimaryKeyAttribute) != null && !string.IsNullOrEmpty((primaryKeyAttribute[0] as PrimaryKeyAttribute).PrimaryKey))
            {
                primaryKeyName = (primaryKeyAttribute[0] as PrimaryKeyAttribute).PrimaryKey;
            }
            else
            {
                primaryKeyName = primaryKeys[0].Name;
            }

            return primaryKeyName;
        }


        public void Delete()
        {
            var type = typeof(T);
            var table = GetTableNameFromType(type);
            var properties = type.GetProperties();
            var primaryKeyName = GetPrimaryKeyName(properties);
            var primaryKeyValue = (Guid)GetPrimaryKeyProperty(properties).GetValue(this, null);

            var query = "DELETE FROM " + table + " WHERE " + primaryKeyName + "='" + primaryKeyValue + "'";

            //FreeQueryNoResults(query);
            ExecuteNonQueryResults(query);


        }

        public bool Save()
        {
            var type = typeof(T);
            var table = GetTableNameFromType(type);
            var properties = type.GetProperties();

            if (_isNew)
            {
                var dbColumnNames = new List<string>();
                var values = new List<object>();
                var dbColumnValues = new List<string>();

                Array.ForEach(properties, x =>
                                              {
                                                  dbColumnNames.Add("[" + x.Name + "]");

                                                  if (_autoDateProperties.Contains(x.Name.ToLower()))
                                                  {
                                                      var rightNow = DateTime.Now;
                                                      values.Add(rightNow);
                                                      x.SetValue(this, rightNow, null);
                                                  }
                                                  else
                                                  {
                                                      values.Add(x.GetValue(this, null));
                                                  }
                                              }



                    );

                foreach (var value in values)
                {
                    dbColumnValues.Add(GetSqlValueFromObject(value));

                }

                InsertRow(table, dbColumnNames, dbColumnValues);
                _isNew = false;

            }
            else
            {
                var primaryKeyValue = Guid.Empty;
                var primaryKeyName = GetPrimaryKeyName(properties);

                var updates = new List<string>();

                foreach (var property in properties)
                {
                    var propertyValue = property.GetValue(this, null);

                    if (property.Name == primaryKeyName)
                    {
                        primaryKeyValue = (Guid)propertyValue;

                        if (primaryKeyValue == Guid.Empty)
                            throw new Exception("The primary key value cannot be an empty Guid.");

                        continue;
                    }
                    if (_autoUpdateDateProperties.Contains(property.Name.ToLower()))
                    {
                        var rightNow = DateTime.Now;
                        updates.Add(property.Name + "='" + rightNow + "'");
                        property.SetValue(this, rightNow, null);
                        continue;
                    }

                    //if (_autoUpdateUserIdProperties.Contains(property.Name.ToLower()) && CommonConfiguration.CurrentUser != null)// == "updatedby" || property.Name.ToLower() == "lastupdatedby") && CommonConfiguration.CurrentUser != null)
                    //{
                    //    updates.Add(property.Name + "='" + CommonConfiguration.CurrentUser.UserId + "'");
                    //    property.SetValue(this, CommonConfiguration.CurrentUser.UserId, null);
                    //    continue;
                    //}

                    updates.Add("[" + property.Name + "]" + "=" + GetSqlValueFromObject(propertyValue));
                }

                UpdateRow(table, updates, primaryKeyName + "='" + primaryKeyValue + "'");
            }


            return true;
        }

        public void InsertRow(string tableName, List<string> columnName, List<string> values)
        {
            var query = new StringBuilder();
            query.Append("INSERT INTO ");
            query.Append(tableName);
            query.Append(" WITH(ROWLOCK) (");
            query.Append(string.Join(",", columnName.ToArray()));
            query.Append(") VALUES (");
            query.Append(string.Join(",", values.ToArray()));
            query.Append(")");

            ExecuteNonQueryResults(query.ToString());
        }

        public void UpdateRow(string tableName, List<string> updateStrings, string whereClause)
        {
            var query = new StringBuilder();
            query.Append("UPDATE ");
            query.Append(tableName);
            query.Append(" WITH (ROWLOCK) SET ");
            query.Append(string.Join(",", updateStrings.ToArray()));
            query.Append(" WHERE ");
            query.Append(whereClause);

            ExecuteNonQueryResults(query.ToString());

        }

        private void ExecuteNonQueryResults(string query)
        {
            using (var connection = new SqlConnection(connectionString))
            {
                using (var command = new SqlCommand())
                {
                    command.CommandText = query;
                    command.CommandType = CommandType.Text;
                    command.CommandTimeout = 10; //sec 
                    command.Connection = connection;
                    connection.Open();
                    command.ExecuteNonQuery();
                }
            }

        }

       


        public static IList<T> GetFullTable()
        {
            var table = GetTableNameFromType(typeof(T));
            var ds = SelectFullTable(table);

            return GetRecordsFromDataSet(ds);
        }



        private static IList<T> GetRecordsFromDataSet(DataSet ds)
        {
            var records = new List<T>();

            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                records.Add(GetRecordFromDataRow(dr));
            }

            return records;
        }

        private static T GetRecordFromDataRow(DataRow dr)
        {
            var record = Activator.CreateInstance<T>();

            var properties = typeof(T).GetProperties();

            foreach (var property in properties)
            {
                object value = dr[property.Name.ToLower()];

                if (value == DBNull.Value)
                    value = property.GetType().IsValueType ? Activator.CreateInstance(property.GetType()) : null;

                property.SetValue(record, value, null);
            }

            record._isNew = false;

            return record;
        }

        private static DataSet RetrieveDataSet(string query)
        {
            using (var connection = new SqlConnection(connectionString))
            {
                using (var command = new SqlCommand())
                {
                    command.CommandText = query;
                    command.CommandType = CommandType.Text;
                    command.CommandTimeout = 10;
                    command.Connection = connection;

                    connection.Open();

                    var dataSet = new DataSet();
                    var adapter = new SqlDataAdapter { SelectCommand = command };
                    adapter.Fill(dataSet);
                    return dataSet;
                }
            }

        }

        public static DataSet SelectFullTable(string tableName)
        {
            var query = new StringBuilder();
            query.Append("SELECT * FROM ");
            query.Append(tableName);

            return RetrieveDataSet(query.ToString());
        }

        public static DataSet SelectFullTable(string tableName, string whereClause)
        {
            var query = new StringBuilder();
            query.Append("SELECT * FROM ");
            query.Append(tableName);
            query.Append(" WITH (NOLOCK) WHERE ");
            query.Append(whereClause);

            return RetrieveDataSet(query.ToString());
        }

        public static DataSet SelectFullTable(string tableName, string whereClause, string sort)
        {
            var query = new StringBuilder();
            query.Append("SELECT * FROM ");
            query.Append(tableName);
            query.Append(" WITH (NOLOCK) WHERE ");
            query.Append(whereClause);
            query.Append("ORDER BY ");
            query.Append(sort);

            return RetrieveDataSet(query.ToString());


        }


        public static int GetRowCountByCustomQuery(string where)
        {
            var table = GetTableNameFromType(typeof(T));
            var primaryKeyName = GetPrimaryKeyName(typeof(T).GetProperties());

            var count = GetRowCount(table, primaryKeyName, where);

            return count;
        }

        public static int GetRowCount(string tableName, string countIndex, string whereClause)
        {
            var query = new StringBuilder();
            query.Append("SELECT COUNT(");
            query.Append(countIndex);
            query.Append(") FROM ");
            query.Append(tableName);
            query.Append(" WHERE ");
            query.Append(whereClause);

            return (int)RetrieveScalar(query.ToString());
        }

        private static object RetrieveScalar(string query)
        {
            using (var connection = new SqlConnection(connectionString))
            {
                using (var command = new SqlCommand())
                {
                    command.CommandText = query;
                    command.CommandType = CommandType.Text;
                    command.Connection = connection;
                    command.CommandTimeout = 10; //sec 

                    connection.Open();
                    return command.ExecuteScalar();
                }
            }

        }

        public static T GetById(Guid id)
        {
            var table = GetTableNameFromType(typeof(T));
            var primaryKeyName = GetPrimaryKeyName(typeof(T).GetProperties());

            var ds = SelectFullTable(table, primaryKeyName + "='" + id + "'");
            var records = GetRecordsFromDataSet(ds);

            return records.Count > 0 ? GetRecordsFromDataSet(ds)[0] : null;
        }

        public static IList<T> GetByCustomQuery(string whereClause)
        {
            var table = GetTableNameFromType(typeof(T));

            var ds = SelectFullTable(table, whereClause);

            return GetRecordsFromDataSet(ds);
        }

        public static IList<T> GetByCustomQuery(string whereClause, string orderBy)
        {
            var table = GetTableNameFromType(typeof(T));

            var ds = SelectFullTable(table, whereClause, orderBy);

            return GetRecordsFromDataSet(ds);
        }













    }


    public class PrimaryKeyAttribute : Attribute
    {
        public string PrimaryKey { get; set; }
    }

    public class TableNameAttribute : Attribute
    {
        public string TableName { get; set; }

        public TableNameAttribute(string tableName)
        {
            TableName = tableName;
        }
    }

}